﻿-- =============================================
-- Author:		<赵洪涛>
-- Edit date:	<2012-09-18>
-- Description:	<销售业绩 赵洪涛 2012-09-18/2014-08-09/2015-01-06 重新编写>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_Jixiao_TongjiOne]
	(
		@CompanyId int,
		@DepartmentId int,
		@EmpState int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@EName nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	Declare @cSql nvarchar(4000),@cWhere nvarchar(1000),@cWhere2 nvarchar(1000)
	
	If @EmpState=2
		Set @EmpState = 5

	Set @cWhere = 'CompanyId='+Convert(varchar(10),@CompanyId)+' And NOE_Flag=0 And CreateDate>=@sRq And CreateDate<=@eRq
		And DeptId In(Select Id From Department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)'
	If LTrim(RTrim(@EName)) <> ''
		Set @cWhere = @cWhere + ' And Salesman='''+@EName+''''
		
	If @EmpState>0
		Set @cWhere2 = ' And State='+Convert(varchar(10),@EmpState)
	Else
		Set @cWhere2 = ''

	Set @cSql='
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')

	Declare @Company nvarchar(50),@bmbh_T varchar(30)
	Select @Company=CompanyName From Company Where Id='+Convert(varchar(10),@CompanyId)+'
	Select @bmbh_T=bmbh From department Where id='+Convert(varchar(10),@DepartmentId)+'

	;WITH List1 As (
		Select DeptId,SalesmanAccount,
		IsNull(Sum(SalePrice)-Sum(CostPrice),0) As Sum_CostPrice
		From Project
		Where '+@cWhere+'
		Group By DeptId,SalesmanAccount
	),	--取这些员工指定日期的毛利(含变更)
	List2 As (
		Select DepId As DeptId,SalesmanAccount,
		Count(0) As Count_OrderId,Sum(YingShouPrice) As Sum_SalePrice
		From [Order]
		Where '+Replace(@cWhere,'DeptId','DepId')+' And FinanceRenLingDate Is Not Null
		Group By DepId,SalesmanAccount
	),	--取这些员工指定日期的成单数和成单总额(未认款的不参与计算)
	List4 As (
		Select IsNull(List2.DeptId,List1.DeptId) As DeptId,
		IsNull(List2.SalesmanAccount,List1.SalesmanAccount) As SalesmanAccount,
		IsNull(List2.Count_OrderId,0) As Count_OrderId,
		IsNull(List2.Sum_SalePrice,0) As Sum_SalePrice,
		IsNull(List1.Sum_CostPrice,0) As Sum_CostPrice
		From List2 FULL JOIN List1 On List2.SalesmanAccount=List1.SalesmanAccount And List2.DeptId=List1.DeptId
	),
	List3 As (
		Select @Company As Company,
			IsNull((Select DName From Department Where Id=DeptId),'''') As Department,
			(Case When Employee.State=5 Then 5 Else 1 End) As State,Employee.GID,EName,
			List4.SalesmanAccount As UserName,
			List4.Count_OrderId As Order_Count,
			List4.Sum_SalePrice As Order_FullPrice,
			List4.Sum_CostPrice As MaoLi,
			(Select Sum(Sum_SalePrice) From List4) As Order_FullPrice_Sum,	--部门的成单总额
			(Select Sum(Sum_CostPrice) From List4) As CostPrice_Sum,		--部门的毛利总额
			(Select Sum(Count_OrderId) From List4) As Order_Count_Sum	--部门的成单总数
		From List4
		Left Join Employee On List4.SalesmanAccount = Employee.UserName
	),	--按员工状态取毛利、成单总额、成单数，并计算部门的成单总额之和、毛利之和、成单数之和(公司编号、部门编号、日期等条件上面已限制，此处不需要再加)
	List As (
		Select ROW_NUMBER() OVER (Order By MaoLi Desc,Order_FullPrice Desc,Order_Count Desc,UserName) As Row,
		* From List3 Where (Order_Count>0 Or Order_FullPrice>0 Or MaoLi<>0)'+@cWhere2+'
	)	--该取的数据都有了，此处只做了排序，供分页用
	Select *,(Select Count(0) From List) As Count_Sum
	From List Where Row>='+Convert(varchar(10),@StartIndex)+' And Row<='+Convert(varchar(10),@EndIndex)+'
	Order By Row'

	Exec(@cSql)
End
